package cn.sccl.common.util;

import cn.sccl.common.scaffold.ColumnInfo;
import com.mysql.jdbc.*;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by 杨浩宇 on 2016-02-19.
 */
public class DataBaseUtils {

    public final static String SET_METHOD = "set";
    public final static String GET_METHOD = "get";

    /**
     * 将model转换为hashMap，非空值才更新
     *
     * @param objModel 模型
     * @return hashMap
     */
    public static HashMap<String, Object> Model2HashMap(Object objModel) {
        return Model2HashMap(objModel, null);
    }


    /**
     * 将Model类转换成HashMap，有值的字段才转换
     *
     * @param objModel      要转换的model
     * @param aryNullFields 需要保持为null值的属性数组
     * @return HashMap: 供ibatis底层使用的更新Map
     */
    public static HashMap<String, Object> Model2HashMap(Object objModel, String[] aryNullFields) {
        String methodName = "";
        java.lang.reflect.Method method = null;
        HashMap<String, Object> hsResult = new HashMap<String, Object>(10);
        HashMap<String, Object> mapNullFields = null;

        if (aryNullFields != null) {
            mapNullFields = new HashMap<String, Object>(aryNullFields.length);
            for (int i = 0; i < aryNullFields.length; i++) {
                mapNullFields.put(aryNullFields[i], null);
            }
        }

        try {
            // 循环该类的所有方法
            for (int i = 0; i < objModel.getClass().getMethods().length; i++) {
                method = objModel.getClass().getMethods()[i];
                methodName = method.getName();
                // 过滤
                if (methodName.indexOf(GET_METHOD) == 0) {
                    Object objResult = method.invoke(objModel, new Object[]{});
                    String strName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4);
                    // 如果返回的非null值
                    if (objResult != null) {
                        if ("getClass".equals(methodName) == true)
                            continue;

                        hsResult.put(strName, objResult);
                    } else if (aryNullFields != null && mapNullFields.containsKey(strName)) {
                        hsResult.put(strName + "_set_null", true);//如果属性值为空，但属性名称存在于aryNullFields中，则在map中设置一个“属性名_set_null”元素，mybatis通过此元素判断是否执行set xxx = null语句
                    }

                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return hsResult;
    }

    /**
     * 将查询出的数据集转换成Model对象
     *
     * @param cls            需要转换的对象
     * @param rs             数据集
     * @param bStrictMaching 是否严格匹配
     * @param <T>
     * @return
     */
    public static <T> List<T> FillModel(Class<T> cls, ResultSet rs, Boolean bStrictMaching) {

        ResultSetMetaData rsmd = null;
        List<T> list = null;
        try {
            // 获取元数据
            rsmd = rs.getMetaData();
            int iColumnCount = rsmd.getColumnCount();
            // 定义字段名称数组
            String[] aryFieldName = new String[iColumnCount];
            // 定义字段类型数组
            Class[] aryFieldType = new Class[iColumnCount];

            for (int iIdx = 0; iIdx < iColumnCount; iIdx++) {
                // 转换成java 名称
                aryFieldName[iIdx] = ColumnInfo.toPropertyName(rsmd.getColumnLabel(iIdx + 1));
                // 转换成java类型
                aryFieldType[iIdx] = ColumnInfo.toJavaType(rsmd.getColumnTypeName(iIdx + 1), rsmd.getScale(iIdx + 1), rsmd.getPrecision(iIdx + 1));
            }
//
//			// 得到行数
//			rs.last();
//			int iRowCount = rs.getRow();
//			rs.beforeFirst();

            // 初始化每行
            long lRowCount = 0;
            list = new ArrayList<T>();
            while (rs.next()) {
                // 行记录转换成model
                list.add(row2Model(rs, aryFieldName, aryFieldType, cls, iColumnCount, bStrictMaching));
                lRowCount++;
                // 最大输出只能是10000条记录
                if (lRowCount > 10000)
                    break;
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public static <T> T row2Model(ResultSet rs, String[] aryFieldName, Class[] aryFieldType, Class<T> cls,
                                  int iColumnCount, Boolean bStrictMaching) throws Exception {
        T obj = cls.newInstance();
        for (int iColIdx = 1; iColIdx <= iColumnCount; iColIdx++) {
            Object filedObj = null;
            if (aryFieldType[iColIdx - 1].equals(Timestamp.class)) {
                filedObj = rs.getTimestamp(iColIdx);
            } else if (aryFieldType[iColIdx - 1].equals(java.util.Date.class)) {// sql.Date->util.Date存在 hh:mi:ss部分丢失
                filedObj = rs.getObject(iColIdx);
            } else if (rs.getObject(iColIdx) != null && rs.getObject(iColIdx).getClass().getName().contains("CLOB")) {// clob流数据读取
                filedObj = StrUtils.clob2String((Clob) rs.getClob(iColIdx));
            } else if (aryFieldType[iColIdx - 1].equals(boolean.class)) {
                filedObj = rs.getBoolean(iColIdx);
            } else if (aryFieldType[iColIdx - 1].equals(byte.class) &&rs.getObject(iColIdx).getClass().getName().contains("[B")){
                filedObj = ((Blob) rs.getBlob(iColIdx));
            } else if (rs.getObject(iColIdx) != null) {
                Constructor constructor = aryFieldType[iColIdx - 1].getDeclaredConstructor(String.class);
                filedObj = constructor.newInstance(rs.getObject(iColIdx).toString());
            }
            // 这句代码有问题，rs.getObject(iColIdx),就有可能为null
            // else{
            // DevLog.debug(iColIdx+"rs.getObject(iColIdx)=="+rs.getObject(iColIdx));
            // throw new RuntimeException("转换失败！可能是使用了oracle 聚合函数，无法映射数据类型");
            // }
            if (filedObj != null) {
                boolean flag = setField2Model(filedObj, aryFieldName[iColIdx - 1], obj, aryFieldType[iColIdx - 1], cls);
                if (!flag && bStrictMaching) {
                    throw new Exception("[" + aryFieldType[iColIdx - 1].getName() + ":" + aryFieldName[iColIdx - 1]
                            + "]在model中不存在同名属性或者属性类型不一致");
                }
            }
        }
        return obj;
    }

    public static <T> boolean setField2Model(Object val, String fieldName, T obj, Class fieldType, Class cls) {
        boolean flag = Boolean.FALSE;
        try {
            // 匹配模型中同名的属性
            if ("rownonum".equals(fieldName)) {// 如果使用了分页查询的，则剔除rownonum进行封装 zhouxinxi edit at:20131210
                return true;
            }
            Field field = cls.getDeclaredField(fieldName);
            field.setAccessible(Boolean.TRUE);
            // 匹配类型是否一致
            if (field.getType().equals(fieldType)) {
                // 写入属性值
                field.set(obj, val);
            }else if (fieldType.equals(byte.class) && field.getType().getName().contains("[B")){
                Blob blob = (Blob)val;
                InputStream is = blob.getBinaryStream();
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                int i = -1;
                while ((i = is.read()) != -1) {
                    baos.write(i);
                }
                field.set(obj, baos.toByteArray());
            } else if (field.getType().equals(String.class)) {
                field.set(obj, val.toString());
            } else if ((fieldType.equals(Long.class) && field.getType().equals(Integer.class))) {
                // 写入属性值
                field.set(obj, ((Long) val).intValue());
            } else if ((fieldType.equals(Long.class) && field.getType().equals(BigDecimal.class))) {
                // 写入属性值
                field.set(obj, new BigDecimal((Long) val));
            }
            flag = Boolean.TRUE;
        } catch (NoSuchFieldException e) {
            //
        } catch (Exception e) {
            DevLog.debug(fieldName + fieldType);
            e.printStackTrace();
        }
        if (!flag && !cls.getSuperclass().equals(Object.class)) {
            return setField2Model(val, fieldName, obj, fieldType, cls.getSuperclass());
        }
        return flag;
    }

    /**
     * 将sql转换为分页查询的sql
     *
     * @param sql
     * @param pageNo
     * @param pageSize
     * @return
     */
    public static String getPagedSQL(String sql, long pageNo, long pageSize) {
        StringBuffer pageSql = new StringBuffer();
        if (PropertiesUtil.getString("jdbc.type").equals(Const.ORACLE)) {
            long start = (pageNo - 1) * pageSize;
            pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
            pageSql.append(sql);
            //pageSql.append(") as tmp_tb where ROWNUM<=");
            pageSql.append(") tmp_tb where ROWNUM<=");
            pageSql.append(start + pageSize);
            pageSql.append(") where row_id>");
            pageSql.append(start);
        } else {
            // for mysql
            long start = (pageNo - 1) * pageSize;
            pageSql.append(sql);
            pageSql.append(" limit ");
            pageSql.append(start);
            pageSql.append(",");
            pageSql.append(pageSize);
//            pageSql.append("select tmp_ta.* from (SELECT @rownum:=@rownum+1 As rownum, tmp_tb.* \n" +
//                    "FROM (SELECT @rownum:=0) r, (");
//            pageSql.append(sql);
//            pageSql.append(") tmp_tb WHERE @rownum<");
//            pageSql.append(start + pageSize);
//            pageSql.append(") tmp_ta where tmp_ta.rownum>");
//            pageSql.append(start);
        }
        return pageSql.toString();
    }

    /**
     * 获取分页查询中的count查询的sql
     *
     * @param sql
     * @return
     */
    public static String getCountSQL(String sql) {
        return "select count(0) as total from (" + removeOrders(sql) + ")  tmp_count";
    }

    /**
     * 去掉hql中的order By子句，用于分页查询
     *
     * @param hql
     * @return
     */
    private static String removeOrders(String hql) {
        Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(hql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "");
        }
        m.appendTail(sb);
        return sb.toString();
    }
}
